package com.wustl.android.hood;

import java.io.FileNotFoundException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class DbActions {
	public static final String KEY_ROWID="_id";
    public static final String KEY_LABEL="label";
    public static final String KEY_DTCREATED="dtcreated";
    public static final String KEY_APPICONPATH = "iconpath";
    public static final String KEY_APPURL = "appurl";
    public static final String KEY_USERNAME = "username";
    public static final String KEY_PASSWD = "passwd";
    public static final String KEY_EXECCOUNT = "execcount";
    
    
    /**
     * Database creation sql statement
     */
    private static final String DATABASE_CREATE =
        "create table locations (_id integer primary key autoincrement, "
            + KEY_LABEL + " text not null, "
            + KEY_DTCREATED +" smalldatetime default getdate(), "
            + KEY_APPICONPATH +" varchar(200) null, "
            + KEY_APPURL +" varchar(1000) null, "
            + KEY_USERNAME +" varchar(100)  null, "
            + KEY_PASSWD +" varchar(50) null, "
            + KEY_EXECCOUNT +" int not null default 0); "
            ;

    private static final String DATABASE_NAME = "hood";
    private static final String DATABASE_TABLE = "Action";
    private static final int DATABASE_VERSION = 2;

    private SQLiteDatabase mDb;
    private final Context mCtx;
    
    public DbActions(Context ctx) {
        this.mCtx = ctx;
    }
    
    public DbActions open() throws SQLException {
        try {
            mDb = mCtx.openDatabase(DATABASE_NAME, null);
        } catch (FileNotFoundException e) {
            try {
                mDb = mCtx.createDatabase(DATABASE_NAME, DATABASE_VERSION, 0, null);
                mDb.execSQL(DATABASE_CREATE);
            } catch (FileNotFoundException e1) {
                throw new SQLException("Could not create database");
            }
        }
        return this;
    }

    public void close() {
        mDb.close();
    }

    /**
     * Create a new Action using the title and GPS locations provided.
     * If the Action is successfully created return the new rowId, otherwise return -1
     * @param label	the label of the action
     * @param iconpath local path for application icon (to make up the display)
     * @param apprul url for authentication of target application
     * @param username	user credential for authentication
     * @param passwd	user credential for authentication
     * @return
     */
    public long createAction(String label, String appiconpath,String appurl, String username, String passwd) {
    	/* how can I pass a getdate() equivalent?*/
    	ContentValues initialValues = new ContentValues();
    	initialValues.put(KEY_LABEL, label);
    	initialValues.put(KEY_APPICONPATH, appiconpath);
    	initialValues.put(KEY_APPURL, appurl);
    	initialValues.put(KEY_USERNAME, username);
    	initialValues.put(KEY_PASSWD, passwd);
    	initialValues.put(KEY_EXECCOUNT, 0);
    	return mDb.insert(DATABASE_TABLE, null, initialValues);
    }

    /**
     * Delete the location with the given rowId
     * @param rowId id of location to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteAction(long rowId) {
        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    /**
     * Return a Cursor over the list of all locations in the database
     * @return Cursor over all locations
     */
    public Cursor fetchAllLocations() {
        return mDb.query(DATABASE_TABLE, new String[] {
                KEY_ROWID, KEY_LABEL, KEY_DTCREATED, KEY_APPICONPATH, KEY_APPURL, KEY_USERNAME, KEY_PASSWD,KEY_EXECCOUNT }, null, null, null, null, null);
    }
    
    public Cursor fetchAllLocations( String[] columns) {
    	return mDb.query(DATABASE_TABLE, columns, null, null, null, null, null);
    }
    
    public Cursor fetchAllLocationsID() {
    	return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID}, null, null, null, null, null);
    }

    /**
     * Return a Cursor positioned at the action that matches the given rowId
     * @param rowId id of location to retrieve
     * @return Cursor positioned to matching location, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchAction(long rowId) throws SQLException {
        Cursor result = mDb.query(true, DATABASE_TABLE, new String[] {
        		KEY_ROWID, KEY_LABEL, KEY_DTCREATED,KEY_APPICONPATH,  KEY_APPURL, KEY_USERNAME, KEY_PASSWD,KEY_EXECCOUNT}, KEY_ROWID + "=" + rowId, null, null,
                null, null);
        if ((result.count() == 0) || !result.first()) {
            throw new SQLException("No location matching ID: " + rowId);
        }
        return result;
    }

    public boolean updateAction(long rowId, String label, String appurl, String username, String passwd) {
        ContentValues args = new ContentValues();
        args.put(KEY_LABEL, label);
        args.put(KEY_APPURL, appurl);
        args.put(KEY_USERNAME, username);
        args.put(KEY_PASSWD, passwd);
        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
    public boolean updateActionExecCount(long rowId) {
//        ContentValues args = new ContentValues();
//        args.put(KEY_EXECCOUNT, label);
//        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    	return false;
    }
}

